

Dim param_date As Date    ' 日期
Dim param_code As String  ' 物件编码

Dim objSheet1 As Worksheet
Dim objSheet2 As Worksheet
Dim dst_colNo As Integer
Dim date_rowNo As String
Dim total_demand As Integer   ' 总订单需求
Dim total_delivery As Integer ' 总送货量

Rem 根据列号转为列编码
Function num_to_code(h)
    a = h: bb = ""
    Do
        i = Int(a / 26)
        r = a Mod 26
        If r <> 0 Then
            bb = Chr(r + 64) & bb
        Else
            bb = "Z" & bb
        End If
        a = i
    Loop While i > 0
    num_to_code = bb
End Function


Public Sub deal_date()
  Set objSheet1 = ThisWorkbook.Sheets("sheet1")  ' 结果表
  Set objSheet2 = ThisWorkbook.Sheets("总表")    ' 总表
    
  Rem 循环到A列最后一行
  For RowIndex = 7 To Range("a65536").End(xlUp).Row
    param_date = objSheet1.Range("A" & RowIndex).Value
    param_code = objSheet1.Range("B" & RowIndex).Value
    
    Rem dst_colNo = Split(objSheet2.Range("C:C").Find(param_code).Address, "$")(2)
    Rem date_rowNo = Split(objSheet2.Range("A2:N2").Find(param_date).Address, "$")(1)
    dst_colNo = objSheet2.Range("C:C").Find(param_code).Row
    date_rowNo = objSheet2.Range("A2:N2").Find(param_date).Column
    
    Rem objSheet2.Range("A1").Value = param_date
    Rem objSheet2.Range("B1").Value = param_code
    
    total_demand = WorksheetFunction.Sum(objSheet2.Range("E" & dst_colNo & ":" & num_to_code(date_rowNo) & dst_colNo))
    Rem total_delivery = WorksheetFunction.Sum(objSheet2.Range("E" & (dst_colNo + 1) & ":" & num_to_code(date_rowNo) & (dst_colNo + 1)))
    Dim index As Integer
    For index = 0 To 15
      total_delivery = WorksheetFunction.Sum(objSheet2.Range("E" & (dst_colNo + 1) & ":" & num_to_code(date_rowNo + index) & (dst_colNo + 1)))
      If total_delivery >= total_demand And total_delivery <> 0 Then
      Exit For
      End If
    Next
      
    Rem Debug.Print date_rowNo, dst_colNo, total_demand, total_delivery, num_to_code(date_rowNo + index), index, objSheet2.Range(num_to_code(date_rowNo + index) & "2").Value
    If index >= 15 Then
      objSheet1.Range("E" & RowIndex).Value = ""
    Else
     objSheet1.Range("E" & RowIndex).Value = objSheet2.Range(num_to_code(date_rowNo + index) & "2").Value
    End If

  Next
  
  
End Sub
